package com.sys.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.sys.dao.UserDao;
import com.sys.domain.LegalUsers;
import com.sys.domain.User;
import com.sys.utils.C3P0Utils;

/**
 * @ClassName: UserDaoImpl 
 * @Description: TODO(实现接口UserDao提供的服务) 
 * @author 詹金浩 
 * @date 2018年3月8日 下午12:34:17
 */
public class UserDaoImpl implements UserDao {

	//使用dbutils和C3P0utils操作数据库
	private QueryRunner queryRunner;
	public UserDaoImpl() {
		queryRunner = new QueryRunner(C3P0Utils.getDataSource());
	}
	
	@Override
	public Boolean add(User user) throws SQLException {
		String	sql = "insert into users values(?,?,?,?,?)";
		int row = queryRunner.update(sql, user.getUserId(), user.getUserName(), user.getUserPwd(),
				user.geteMail(), user.getStatus());
		return row > 0? true:false;
	}

	@Override
	public Boolean delete(User user) throws SQLException {
		String sql = "delete from users where user_id=?";
		return queryRunner.update(sql, user.getUserId())>0?true:false;
	}

	@Override
	public User findUserByUserId(String userId) throws SQLException {
		String sql = "select user_id as userId, user_name as userName, user_pwd as userPwd, e_mail as eMail, status as status from users where user_id = ?";
		return queryRunner.query(sql, new BeanHandler<User>(User.class), userId);
	}

	@Override
	public Boolean changeUserPwdByUserId(String userId, String newPwd) throws SQLException {
		String sql = "update users set user_pwd = ? where user_id = ?";
		return queryRunner.update(sql, newPwd, userId)>0?true:false;
	}

	@Override
	public Boolean isUserIdLegal(String userId) throws SQLException {
		String sql = "select * from legal_users where user_id=?";
		return queryRunner.query(sql, new BeanHandler<LegalUsers>(LegalUsers.class), userId)!=null ? true:false;
	}

	@Override
	public Boolean isEmailExist(String eMail) throws SQLException {
		String sql = "select e_mail as eMail from users where e_mail=?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), eMail);
		return user!=null?true:false;
	}

	@Override
	public Boolean findUserByIdAndPwd(String userId, String userPwd) throws SQLException {
		String sql = "select user_id as userId, user_pwd as userPwd from users where user_id=? and user_pwd=?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), userId, userPwd);
		return user!=null?true:false;
	}

	@Override
	public Boolean findUserByEmailAndPwd(String eMail, String userPwd) throws SQLException {
		String sql = "select e_mail as eMail, user_pwd as userPwd from users where e_mail=? and user_pwd=?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), eMail, userPwd);
		return user!=null?true:false;
	}

	@Override
	public String getUserIdByEmailDao(String eMail) throws SQLException {
		String sql = "select user_id as userId from users where e_mail=?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), eMail);
		return user.getUserId();
	}

	@Override
	public Boolean checkUserInfoWhoChangePwd(String userId, String eMail, String userPwd) throws SQLException {
		String  sql = "select *from users where user_id = ? and e_mail = ? and user_pwd = ?";
		return queryRunner.query(sql, new BeanHandler<User>(User.class), userId, eMail, userPwd)!=null?true:false;
	}

	@Override
	public List<User> allUsers() throws SQLException {
		String sql = "select user_id as userId, user_name as userName, e_mail as eMail, status as status from users";
		List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class));
		return users;
	}

	@Override
	public Boolean changeStatus(String userId, String status) throws SQLException {
		String sql = "update users set status = ? where user_id = ?";
		int row = queryRunner.update(sql, status, userId);
		return row>0?true:false;
	}

	@Override
	public String getStatus(String userId) throws SQLException {
		String sql = "select status as status from users where user_id = ?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), userId);
		System.out.println(user);
		return user.getStatus();
	}
}
